package com.cw.jeeyt.service.impl.api;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.cw.jeeyt.common.CommonUtil;
import com.cw.jeeyt.common.Order;
import com.cw.jeeyt.common.enums.EnumDataJavaType;
import com.cw.jeeyt.common.enums.EnumDatabaseType;
import com.cw.jeeyt.common.enums.EnumMysqlDataType;
import com.cw.jeeyt.service.bo.api.ApiDataEntryBo;
import com.cw.jeeyt.service.bo.security.SecurityUserBo;
import com.cw.jeeyt.service.bo.system.SystemDatabaseBo;
import com.cw.jeeyt.core.util.SessionUtil;
import com.cw.jeeyt.dao.intf.api.ApiDataEntryDao;
import com.cw.jeeyt.dao.model.api.ApiDataEntryDo;
import com.cw.jeeyt.service.intf.api.ApiDataEntryService;
import com.cw.jeeyt.service.converter.api.ApiDataEntryConverter;
import com.cw.jeeyt.service.intf.system.SystemDatabaseService;
import com.cw.jeeyt.service.utils.DataDicUtils;
import com.cw.lang.common.utils.CollectionUtil;
import com.cw.lang.mybatis.dto.PageInfo;

/**
 * 接口字段信息业务实现类
 *
 * @author chenw
 * @version 1.0.0
 */
@Service
public class ApiDataEntryServiceImpl implements ApiDataEntryService {

    private static final Logger logger = LoggerFactory.getLogger(ApiDataEntryServiceImpl.class);
    @Autowired
    private ApiDataEntryDao apiDataEntryDao;//接口字段信息数据库操作接口
    @Autowired
    private SystemDatabaseService systemDatabaseService;//数据库业务接口

    /**
     * 查询所有接口字段信息
     */
    @Override
    public List<ApiDataEntryBo> queryApiDataEntryList() throws Exception {
        return ApiDataEntryConverter.doList2BoList(apiDataEntryDao.queryApiDataEntryList());
    }

    /**
     * 分页查询接口字段信息
     *
     * @param apiDataEntryBo
     * @param pageNo
     * @param pageSize
     * @return
     * @throws Exception
     */
    @Override
    public PageInfo<ApiDataEntryBo> queryByPage(ApiDataEntryBo apiDataEntryBo, Integer pageNo, Integer pageSize,
        Order order) throws Exception {

        if (apiDataEntryBo == null) {
            logger.error("接口字段信息为空");
            return null;
        }
        PageInfo<ApiDataEntryDo> pageInfo = apiDataEntryDao.queryByPage(ApiDataEntryConverter.bo2Do(apiDataEntryBo),
            pageNo, pageSize, order);
        List<ApiDataEntryBo> apiDataEntryBos = ApiDataEntryConverter.doList2BoList(pageInfo.getList());
        if (CollectionUtil.isNotEmpty(apiDataEntryBos)) {
            Map<String, Object> dictMap = new HashMap<>();
            dictMap.put("columnSqlType", "std_mysql_data_type");
            dictMap.put("columnType", "std_data_java_type");
            JSONArray jsonArray = DataDicUtils.convertDict(JSON.parseArray(JSON.toJSONString(apiDataEntryBos)),
                dictMap);
            apiDataEntryBos = JSON.parseArray(jsonArray.toJSONString(), ApiDataEntryBo.class);
        }
        return new PageInfo<>(pageInfo, apiDataEntryBos);
    }

    /**
     * 根据主键查寻接口字段信息
     *
     * @param id
     * @return
     */
    @Override
    public ApiDataEntryBo getApiDataEntryById(Long id) throws Exception {

        if (null == id) {
            logger.error("--主键id为空--");
            return null;
        }
        return ApiDataEntryConverter.do2Bo(apiDataEntryDao.getById(id));
    }

    /**
     * 新增接口字段信息
     *
     * @param apiDataEntryBo
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer insert(ApiDataEntryBo apiDataEntryBo) throws Exception {

        if (apiDataEntryBo == null) {
            logger.error("接口字段信息为空");
            return null;
        }
        SecurityUserBo userVo = SessionUtil.getSessionUser();
        if (userVo == null) {
            logger.error("用户未登录");
            throw new Exception("用户未登录");
        }
        ApiDataEntryBo checkData = queryByCondition(apiDataEntryBo.getColumnCode(), apiDataEntryBo.getColumnName(),
            apiDataEntryBo.getColumnSqlType());
        if (checkData != null) {
            logger.error("接口字段信息已存在");
            throw new Exception("接口字段信息已存在");
        }
        ApiDataEntryDo apiDataEntry = ApiDataEntryConverter.bo2Do(apiDataEntryBo);
        apiDataEntry.setCreateBy(userVo.getId());
        apiDataEntry.setCreateDate(new Date());
        apiDataEntry.setUpdateBy(userVo.getId());
        apiDataEntry.setUpdateDate(new Date());
        Integer result = apiDataEntryDao.insert(apiDataEntry);
        return result;
    }

    /**
     * 新增接口字段信息不为空的字段
     *
     * @param apiDataEntryBo
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public Integer insertSelective(ApiDataEntryBo apiDataEntryBo) throws Exception {

        if (apiDataEntryBo == null) {
            logger.error("接口字段信息为空");
            return null;
        }
        SecurityUserBo userVo = SessionUtil.getSessionUser();
        if (userVo == null) {
            logger.error("用户未登录");
            throw new Exception("用户未登录");
        }
        ApiDataEntryBo checkData = queryByCondition(apiDataEntryBo.getColumnCode(), apiDataEntryBo.getColumnName(),
            apiDataEntryBo.getColumnSqlType());
        if (checkData != null) {
            logger.error("接口字段信息已存在");
            throw new Exception("接口字段信息已存在");
        }
        ApiDataEntryDo apiDataEntry = ApiDataEntryConverter.bo2Do(apiDataEntryBo);
        apiDataEntry.setCreateBy(userVo.getId());
        apiDataEntry.setCreateDate(new Date());
        apiDataEntry.setUpdateBy(userVo.getId());
        apiDataEntry.setUpdateDate(new Date());
        Integer result = apiDataEntryDao.insertSelective(apiDataEntry);
        return result;
    }

    /**
     * 更新接口字段信息
     *
     * @param apiDataEntryBo
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void update(ApiDataEntryBo apiDataEntryBo) throws Exception {

        if (apiDataEntryBo == null) {
            logger.error("接口字段信息为空");
            return;
        }
        SecurityUserBo userVo = SessionUtil.getSessionUser();
        if (userVo == null) {
            logger.error("用户未登录");
            throw new Exception("用户未登录");
        }
        ApiDataEntryDo apiDataEntry = ApiDataEntryConverter.bo2Do(apiDataEntryBo);
        apiDataEntry.setUpdateBy(userVo.getId());
        apiDataEntry.setUpdateDate(new Date());

        apiDataEntryDao.update(apiDataEntry);
    }

    /**
     * 更新接口字段信息不为空的字段
     *
     * @param apiDataEntryBo
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void updateSelective(ApiDataEntryBo apiDataEntryBo) throws Exception {

        if (apiDataEntryBo == null) {
            logger.error("接口字段信息为空");
            return;
        }
        SecurityUserBo userVo = SessionUtil.getSessionUser();
        if (userVo == null) {
            logger.error("用户未登录");
            throw new Exception("用户未登录");
        }
        ApiDataEntryDo apiDataEntry = ApiDataEntryConverter.bo2Do(apiDataEntryBo);
        apiDataEntry.setUpdateBy(userVo.getId());
        apiDataEntry.setUpdateDate(new Date());

        apiDataEntryDao.updateSelective(apiDataEntry);
    }

    /**
     * 删除接口字段信息
     *
     * @param apiDataEntryBo
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void delete(ApiDataEntryBo apiDataEntryBo) throws Exception {

        if (apiDataEntryBo == null) {
            logger.error("接口字段信息为空");
            return;
        }
        apiDataEntryDao.delete(ApiDataEntryConverter.bo2Do(apiDataEntryBo));
    }

    /**
     * 同步接口字段信息
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void syncDataEntry(String databaseId) throws Exception {
        if (StringUtils.isEmpty(databaseId)) {
            logger.info("--数据库id为空--");
            return;
        }
        SystemDatabaseBo systemDatabaseBo = systemDatabaseService.getSystemDatabaseById(databaseId);
        if (EnumDatabaseType.MYSQL.getCode()
            .equals(systemDatabaseBo.getDatabaseType())) {
            //同步表信息
            Connection connection = getDBConnection(systemDatabaseBo);
            SecurityUserBo user = SessionUtil.getSessionUser();
            List<ApiDataEntryBo> apiDataEntryList = new ArrayList<ApiDataEntryBo>();
            try {
                String queryTableSql
                    = "select table_name tableCode,table_comment tableName from information_schema.tables where table_schema = ?";
                PreparedStatement pstmt = connection.prepareStatement(queryTableSql);
                pstmt.setString(1, systemDatabaseBo.getDatabaseCode());
                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {
                    String tableCode = rs.getString("tableCode");
                    //同步表字段信息
                    StringBuilder queryColumnSql = new StringBuilder(
                        "select distinct column_name columnCode, data_type columnType, column_comment columnName,character_maximum_length columnLength,numeric_precision numericPercision,numeric_scale numericScale,column_key isPrimaryKey from information_schema.columns where table_name = ? and table_schema=?");
                    PreparedStatement columnPstmt = connection.prepareStatement(queryColumnSql.toString());
                    columnPstmt.setString(1, tableCode);
                    columnPstmt.setString(2, systemDatabaseBo.getDatabaseCode());
                    ResultSet rsColumn = columnPstmt.executeQuery();
                    while (rsColumn.next()) {
                        ApiDataEntryBo apiDataEntry = new ApiDataEntryBo();
                        String columnCode = rsColumn.getString("columnCode");
                        String columnName = rsColumn.getString("columnName");
                        String columnSqlType = rsColumn.getString("columnType");
                        //根据字段编码、字段名称、字段类型校验接口字段信息表中是否已存在
                        ApiDataEntryBo checkData = this.queryByCondition(columnCode, columnName, columnSqlType);
                        if (checkData != null) {
                            continue;
                        }
                        if (StringUtils.isNotEmpty(columnCode)) {
                            columnCode = columnCode.toLowerCase();
                        }
                        apiDataEntry.setColumnCode(CommonUtil.getFieldName(columnCode));
                        apiDataEntry.setColumnSqlCode(columnCode);
                        apiDataEntry.setColumnComment(columnName);
                        apiDataEntry.setColumnName(columnName);
                        String columnLength = rsColumn.getString("columnLength");
                        String numericPercision = rsColumn.getString("numericPercision");
                        apiDataEntry.setColumnLength(columnLength);
                        if (StringUtils.isEmpty(columnLength)) {
                            apiDataEntry.setColumnLength(numericPercision);
                        }
                        apiDataEntry.setColumnSqlType(EnumMysqlDataType.find(columnSqlType)
                            .getCode());
                        String javaType = CommonUtil.sqlType2JavaType(columnSqlType);
                        apiDataEntry.setColumnType(EnumDataJavaType.find(javaType)
                            .getCode());
                        apiDataEntry.setCreateBy(user.getId());
                        apiDataEntry.setCreateDate(new Date());
                        apiDataEntry.setUpdateBy(user.getId());
                        apiDataEntry.setUpdateDate(new Date());
                        apiDataEntryList.add(apiDataEntry);
                    }
                }
                if (apiDataEntryList != null && !apiDataEntryList.isEmpty()) {
                    //过滤重复数据
                    List<ApiDataEntryBo> newDataEntryList = new ArrayList<ApiDataEntryBo>();
                    for (ApiDataEntryBo apiDataEntry : apiDataEntryList) {
                        String columnCode = apiDataEntry.getColumnCode();
                        String columnName = apiDataEntry.getColumnName();
                        String columnType = apiDataEntry.getColumnType();
                        boolean exist = false;
                        if (newDataEntryList != null && !newDataEntryList.isEmpty()) {
                            for (ApiDataEntryBo newDataEntry : newDataEntryList) {
                                String new_columnCode = newDataEntry.getColumnCode();
                                String new_columnName = newDataEntry.getColumnName();
                                String new_columnType = newDataEntry.getColumnType();
                                if (new_columnCode.equals(columnCode) && new_columnName.equals(columnName)
                                    && new_columnType.equals(columnType)) {
                                    exist = true;
                                }
                            }
                        }
                        if (!exist) {
                            newDataEntryList.add(apiDataEntry);
                        }
                    }
                    this.insertBat(newDataEntryList);
                }
            } catch (Exception e) {
                e.printStackTrace();
                throw new Exception(e.getMessage());
            } finally {
                if (connection != null) {
                    connection.close();
                }
            }
        }
    }

    /**
     * 根据字段编码、字段名称、字段类型查询接口字段信息
     *
     * @param columnCode
     * @param columnName
     * @param columnSqlType
     * @return
     */
    @Override
    public ApiDataEntryBo queryByCondition(String columnCode, String columnName, String columnSqlType) {
        return ApiDataEntryConverter.do2Bo(apiDataEntryDao.queryByCondition(columnCode, columnName, columnSqlType));
    }

    @Override
    public void insertBat(List<ApiDataEntryBo> apiDataEntryList) throws Exception {
        if (apiDataEntryList == null || apiDataEntryList.isEmpty()) {
            logger.error("要保存接口字段信息为空");
            return;
        }
        apiDataEntryDao.insertBat(ApiDataEntryConverter.boList2DoList(apiDataEntryList));
    }

    /**
     * 获取数据库连接信息
     *
     * @param systemDatabaseBo
     * @return
     * @throws SQLException
     */
    private Connection getDBConnection(SystemDatabaseBo systemDatabaseBo) throws Exception {
        Connection conn = null;
        try {
            Class.forName(systemDatabaseBo.getJdbcDriverClass())
                .newInstance();
            conn = DriverManager.getConnection(systemDatabaseBo.getJdbcUrl(), systemDatabaseBo.getJdbcUserName(),
                systemDatabaseBo.getJdbcUserPassword() == null ? "" : systemDatabaseBo.getJdbcUserPassword());
            if (logger.isDebugEnabled()) {
                logger.debug("Succeed to get a DB connection: " + conn);
            }
        } catch (Exception e) {
            throw new Exception("数据库连接配置有误，请确认再同步：" + e.getMessage());
        }
        return conn;
    }

    @Override
    public PageInfo<ApiDataEntryBo> queryImortByPage(ApiDataEntryBo apiDataEntryBo, Integer pageNo, Integer pageSize,
        Order order, Long objId) throws Exception {
        if (apiDataEntryBo == null) {
            logger.error("接口字段信息为空");
            return null;
        }
        PageInfo<ApiDataEntryDo> pageInfo = apiDataEntryDao.queryImportByPage(
            ApiDataEntryConverter.bo2Do(apiDataEntryBo), pageNo, pageSize, order, objId);
        List<ApiDataEntryBo> apiDataEntryBos = ApiDataEntryConverter.doList2BoList(pageInfo.getList());
        if (CollectionUtil.isNotEmpty(apiDataEntryBos)) {
            Map<String, Object> dictMap = new HashMap<>();
            dictMap.put("columnSqlType", "std_mysql_data_type");
            dictMap.put("columnType", "std_data_java_type");
            JSONArray jsonArray = DataDicUtils.convertDict(JSON.parseArray(JSON.toJSONString(apiDataEntryBos)),
                dictMap);
            apiDataEntryBos = JSON.parseArray(jsonArray.toJSONString(), ApiDataEntryBo.class);
        }
        return new PageInfo<>(pageInfo, apiDataEntryBos);
    }
}
